SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [dbo].[spCalcularVacantes]
@Registro int,
@Numero int output,
@Diagnostico varchar (8000) output

 AS
SET NOCOUNT ON
set @Numero=1
declare @concurso int
set @concurso=@Registro
set @Diagnostico='No se pudo calcular las vacantes para el concurso : ID '+ cast(@concurso as varchar)



delete tb_vacantes where @concurso  is null

--INSERT INTO tb_Vacantes ([Concurso], [Localidad], [Plaza], [PlazaACrear], [Estado], [AgenteDesignado], [SitRevDesignado], [CategoriaTituloDesignado], [FechaDesignacion], [ResponsableDesignacion],[PuntajeDesignacion])
select distinct null as concurso,null as localidad,p.idplaza,null as plazaAcrear,12 as estado,null as agentedesignado,null as sitRevista,null as  CatTitulo,getdate() as FechaDesignacion,null as Responsable,null as PuntajeDesignacion 

FROM         tb_Plazas p INNER JOIN
                      tb_Organizaciones o ON idOrganizaciones = p.Organizacion INNER JOIN
                      tb_Suborganizaciones so ON p.SubOrganizacion = so.IdSubOrganizacion 

WHERE     (P.Baja = 'N') AND P.IdPlaza NOT IN
                          (SELECT     plaza
                            FROM          TB_SUBSERVICIOSAGENTES SSA
                            WHERE      SSA.BAJA = 'N')



/*from tb_subserviciosAgentes ss inner join tb_serviciosagentes s
on ss.servicioagente=s.idServicioAgente inner join tb_plazas p on ss.plaza=p.idplaza
where s.tipodeservicio=1 and s.situacionrevista=2 and
(ss.fechaalta<=getdate()) and ((ss.baja='N') or (ss.baja='S' and ss.fechabaja>=getdate()))
*/
--order by cupof
RETURN
GO
